<?php
/**
 * @copyright Copyright (c) 2021 勾股工作室
 * @license https://opensource.org/licenses/GPL-3.0
 * @link https://www.gougucms.com
 */
 declare (strict_types = 1);
namespace app\api\controller;

use app\api\BaseController;
use think\facade\Db;
use think\facade\Env;
use think\faced\Filesystem;
//use app\user\model\Admin;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date as Shared;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;


class Import extends BaseController
{


/**
 * 批量上传员工信息
 * */
public function import_staff()
{
   
	$files[]= request()->file('file');
    // dd($files);
	try{
		
		   // 验证文件大小，名称等是否正确
		    validate(['xlsfile'=>'filesiz:10*1024*1024|fileExt:xls,xlsx'])->check($files);
            
            $savename = \think\facade\Filesystem::disk('public')->putFile('xls', $files[0]);
           
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            $path = get_config('filesystem.disks.public.url');
            
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load('.'.$path . '/' .$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                return to_assign(1, '数据不能为空');
                exit();
            }

            for ($j = 2; $j <= $highestRow; $j++) {
                $real_name=$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();
                $department=$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue();                
                $status=$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue();
                $phone_number =$objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();
                $phone_number=empty($phone_number) ? '':$phone_number;//因设置了手机号码不为空,当空值时会出现无法导入需要将其设置为空字符串导入

                $data[$j - 2] = [
                    'real_name'      =>$real_name,
                    'department'     =>$department,
                    'status'         =>$status,
                    'phone_number'   =>$phone_number,
                    'signed'         =>'0',
                    'level'          =>'', 
                    'update_time'=>time(),
                    'create_time' =>time(),
                ];
         
            }
             // dd($data);
         
        // 批量添加数据
            $count = Db::name('LotteryStaff')->insertAll($data);
            if ($count) {
                return to_assign(0, '导入成功, 共导入员工数据'.$count.'条');
            }else{
                return to_assign(1, '导入失败，请检查excel文件再试');
            }
	} catch (ValidateException $e) {
			return to_assign(1, $e->getMessage());
    }

}

	//导入签到名单
	public function import_sign(){
        // 获取表单上传文件
        $files[]= request()->file('file');
        // try{
            
               // 验证文件大小，名称等是否正确
                validate(['xlsfile'=>'filesiz:10*1024*1024|fileExt:xls,xlsx'])->check($files);                
                $savename = \think\facade\Filesystem::disk('public')->putFile('xlsx', $files[0]);
               
                $fileExtendName = substr(strrchr($savename, '.'), 1);
                // 有Xls和Xlsx格式两种
                if ($fileExtendName == 'xlsx') {
                    $objReader = IOFactory::createReader('Xlsx');
                } else {
                    $objReader = IOFactory::createReader('Xls');
                }
                $objReader->setReadDataOnly(TRUE);
                $path = get_config('filesystem.disks.public.url');
                
                // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
                $objPHPExcel = $objReader->load('.'.$path . '/' .$savename);
                $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
                $highestRow = $sheet->getHighestRow();       // 取得总行数
                // dump($highestRow);
                $highestColumn = $sheet->getHighestColumn();   // 取得最后列数 H
                // dd($highestColumn);
                $highestColumnIndex=Coordinate::columnIndexFromString($highestColumn);//将列标记转为数字
                //dd($highestColumnIndex);
                 
                $lines = $highestRow - 1;
                if ($lines <= 0) {
                    return to_assign(1, '数据不能为空');
                    exit();
                }
                //读取表头，第几行开始为表头
                $base=2;
                $errorArray=[];
                //$i=1;
                $sign_num=0;

                //循环读取数据
                for ($j = $base; $j <= $highestRow; $j++) {
                    $map=[];                    
                    $map['real_name']=$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();
                    $k=$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();

                 // 也可以用下面的'real_name2'=>$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(2, $j)->getValue()
                   $count=Db::name('LotteryStaff')->where($map)->count();
                   //dump($map);dump($count);

                   if(empty($count)){
                    $errorArray[]=['k'=>$k,'real_name'=>$map['real_name'],'msg'=>'找不到此员工'];
                   }
                   if($count==1){
                    //设置为已签到
                    // $signed=Db::name('LotteryStaff')->where($map)->value('signed');
                    // dump($signed);
                     //$data['signed']='否';
                       $sign_data=['signed'=>'1'];//已签到
                       $res=Db::name('LotteryStaff')->where($map)->save($sign_data);
                      // dump($errorArray);
                      // dd($res);
                      if($res){
                        $user=Db::name('LotteryStaff')->where($map)->field('real_name,phone_number,status')->find();
                        // dump($user);
                            $number=rand(1,16);//取1至16的随机数为头像                        
                             $data=[
                                'real_name'=>$user['real_name'],
                                'phone_number'=>$user['phone_number'],
                                'avatar_url'=>'/avatar/'.$number.'.png',
                                'sign_time'=>date('Y-m-d H:i:s',time()),
                                'status'=>$user['status'],
                                'lottery_allow'=>1,
                                'lottery_done'=>0
                            ];
                            $user_res=Db::name('LotteryUser')->save($data);
                            if($user_res){
                                $sign_num++;
                            }

                        
                      }else{
                        $errorArray[]=['k'=>$k,'real_name'=>$map['real_name'],'msg'=>'此员工已签到'];
                      }
                   }
                   if($count>1){
                      $errorArray[]=['k'=>$k,'real_name'=>$map['real_name'],'msg'=>'员工信息重复,或姓名有雷同'];
                   }

                               
                }
                           
                if(count($errorArray)>0){
                    $info_title=['序号','姓名','导入失败原因说明'];

                    array_unshift($errorArray,$info_title);//向数组首行添加元素
                    // dd($errorArray);
                        set_time_limit(0);
                        $spreadsheet = new Spreadsheet();
                        $worksheet = $spreadsheet->getActiveSheet();
                        $worksheet->fromArray($errorArray,Null,'A1');//第二个参数为与此相等的值不显示

                        $sheet_title='数据导入结果';
                        $worksheet->setTitle($sheet_title);
                        $writer = new Xlsx($spreadsheet);
                        $fileName=date('Y-m-d-H-i-s').'签到信息导入错误记录.xlsx';
                        $path='.\\xlsx\\'.$fileName;
                        $writer->save("$path");
                            $file_data=[
                                'filename'=>$fileName,
                                'filepath'=>$path,
                                'fileext'=>'xlsx',
                                'mimetype'=>'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                                'status'=>1,
                                'create_time'=>time(),
                                'action'=>'Error'];
                            $res=Db::name('FileDownload')->insert($file_data);
                } 

                if ($sign_num) {
                    return to_assign(0, '导入成功, 共导入签到数据'.$sign_num.'条');
                }
                else{
                    return to_assign(1, '导入失败，请检查excel文件再试');
                }
        // } catch (ValidateException $e) {
        //         return to_assign(1, $e->getMessage());
        // }
    }	
	
}
